OptionCalculator

V8 Message Definiton

This table allows custom option pricing based on either user or SR supplied input values.

METADATA

Attribute	Value
Topic	5030-srse-calculators
MLink Token	SystemData
Product	SRAnalytics
accessType	SELECT,UPDATE,INSERT,DELETE

Table Definition

Field	Type	Key	Default Value	Comment
userName	VARCHAR(24)	PRI	''
okey_at	enum - AssetType	PRI	'None'
okey_ts	enum - TickerSrc	PRI	'None'
okey_tk	VARCHAR(12)	PRI	''
okey_yr	SMALLINT UNSIGNED	PRI, SEC	0
okey_mn	TINYINT UNSIGNED	PRI, SEC	0
okey_dy	TINYINT UNSIGNED	PRI, SEC	0
okey_xx	DOUBLE	PRI	0
okey_cp	enum - CallPut	PRI	'Call'
vol	DOUBLE		0	volatility
volSrc	enum - FieldSrc		'Default'
uPrc	DOUBLE		0	underlying price
uPrcSrc	enum - FieldSrc		'Default'
years	DOUBLE		0	yearstoexpiration default uses SR volatility time value
yearsSrc	enum - FieldSrc		'Default'
sdiv	DOUBLE		0	continuous stock dividend using for pricing
sdivSrc	enum - FieldSrc		'Default'
rate	DOUBLE		0	discount rate used for pricing
rateSrc	enum - FieldSrc		'Default'
exType	enum - ExerciseType		'None'	exercise type of the option American or European
exTypeSrc	enum - FieldSrc		'Default'
effStrike	DOUBLE		0	effective strike used for pricing default is okeyStrike
effStrikeSrc	enum - FieldSrc		'Default'
symRatio	DOUBLE		0	underlying symbol ratio for nonstandard options discrete dividend will be scaled by this factor
symRatioSrc	enum - FieldSrc		'Default'
divString	TINYTEXT		''	discrete dividend string yearsToExpiry years1amt1 years2amt2 or 1 date1amt1 date2amt2
divSrc	enum - FieldSrc		'Default'
modelType	enum - CalcModelType		'None'
modelTypeSrc	enum - FieldSrc		'Default'
calcPrecision	enum - CalcPrecision		'Low'	numerical precision of steps if a numerical method is used more steps will be slower to calculate
incGreeks	enum - YesNo		'No'
price	DOUBLE		0	price premium
delta	FLOAT		0	delta
gamma	FLOAT		0	gamma
theta	FLOAT		0	theta
vega	FLOAT		0	vega
volga	FLOAT		0	volga
vanna	FLOAT		0	vanna
deDecay	FLOAT		0	delta decay
rho	FLOAT		0	rho
phi	FLOAT		0	phi
error	VARCHAR(32)		''
pricerModel	VARCHAR(8)		''
timestamp	DATETIME(6)		'2000-01-01'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
userName	1
okey_tk	2
okey_yr	3
okey_mn	4
okey_dy	5
okey_xx	6
okey_cp	7
okey_at	8
okey_ts	9

SECONDARY INDEX (ExpirationIndex) (Not Unique)

Field	Sequence
okey_yr	1
okey_mn	2
okey_dy	3

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRAnalytics`.`MsgOptionCalculator` (
    `userName` VARCHAR(24) NOT NULL DEFAULT '',
    `okey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `okey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `okey_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `okey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `okey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `okey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `okey_xx` DOUBLE NOT NULL DEFAULT 0,
    `okey_cp` ENUM('Call','Put','Pair') NOT NULL DEFAULT 'Call',
    `vol` DOUBLE NOT NULL DEFAULT 0 COMMENT 'volatility',
    `volSrc` ENUM('Default','User') NOT NULL DEFAULT 'Default',
    `uPrc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'underlying price',
    `uPrcSrc` ENUM('Default','User') NOT NULL DEFAULT 'Default',
    `years` DOUBLE NOT NULL DEFAULT 0 COMMENT 'years-to-expiration (default uses SR volatility time value)',
    `yearsSrc` ENUM('Default','User') NOT NULL DEFAULT 'Default',
    `sdiv` DOUBLE NOT NULL DEFAULT 0 COMMENT 'continuous stock dividend using for pricing',
    `sdivSrc` ENUM('Default','User') NOT NULL DEFAULT 'Default',
    `rate` DOUBLE NOT NULL DEFAULT 0 COMMENT 'discount rate used for pricing',
    `rateSrc` ENUM('Default','User') NOT NULL DEFAULT 'Default',
    `exType` ENUM('None','American','European','Asian','Cliquet') NOT NULL DEFAULT 'None' COMMENT 'exercise type of the option (American or European)',
    `exTypeSrc` ENUM('Default','User') NOT NULL DEFAULT 'Default',
    `effStrike` DOUBLE NOT NULL DEFAULT 0 COMMENT 'effective strike used for pricing (default is okey.Strike)',
    `effStrikeSrc` ENUM('Default','User') NOT NULL DEFAULT 'Default',
    `symRatio` DOUBLE NOT NULL DEFAULT 0 COMMENT 'underlying symbol ratio (for non-standard options); discrete dividend will be scaled by this factor',
    `symRatioSrc` ENUM('Default','User') NOT NULL DEFAULT 'Default',
    `divString` TINYTEXT NOT NULL DEFAULT '' COMMENT 'discrete dividend string  [ yearsToExpiry, years1:amt1, years2:amt2, ...] or [ -1, date1:amt1, date2:amt2, ... ]',
    `divSrc` ENUM('Default','User') NOT NULL DEFAULT 'Default',
    `modelType` ENUM('None','LogNormalExact','NormalExact','LogNormalApprox','NormalApprox') NOT NULL DEFAULT 'None',
    `modelTypeSrc` ENUM('Default','User') NOT NULL DEFAULT 'Default',
    `calcPrecision` ENUM('None','Low','Normal','High','Custom') NOT NULL DEFAULT 'Low' COMMENT 'numerical precision (# of steps) if a numerical method is used;  [more steps will be slower to calculate]',
    `incGreeks` ENUM('None','Yes','No') NOT NULL DEFAULT 'No',
    `price` DOUBLE NOT NULL DEFAULT 0 COMMENT 'price (premium)',
    `delta` FLOAT NOT NULL DEFAULT 0 COMMENT 'delta',
    `gamma` FLOAT NOT NULL DEFAULT 0 COMMENT 'gamma',
    `theta` FLOAT NOT NULL DEFAULT 0 COMMENT 'theta',
    `vega` FLOAT NOT NULL DEFAULT 0 COMMENT 'vega',
    `volga` FLOAT NOT NULL DEFAULT 0 COMMENT 'volga',
    `vanna` FLOAT NOT NULL DEFAULT 0 COMMENT 'vanna',
    `deDecay` FLOAT NOT NULL DEFAULT 0 COMMENT 'delta decay',
    `rho` FLOAT NOT NULL DEFAULT 0 COMMENT 'rho',
    `phi` FLOAT NOT NULL DEFAULT 0 COMMENT 'phi',
    `error` VARCHAR(32) NOT NULL DEFAULT '',
    `pricerModel` VARCHAR(8) NOT NULL DEFAULT '',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '2000-01-01',
    PRIMARY KEY USING HASH (`userName`,`okey_tk`,`okey_yr`,`okey_mn`,`okey_dy`,`okey_xx`,`okey_cp`,`okey_at`,`okey_ts`),
    KEY `ExpirationIndex` (`okey_yr`,`okey_mn`,`okey_dy`) USING HASH
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='This table allows custom option pricing based on either user or SR supplied input values.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `userName`,
    `okey_at`,
    `okey_ts`,
    `okey_tk`,
    `okey_yr`,
    `okey_mn`,
    `okey_dy`,
    `okey_xx`,
    `okey_cp`,
    `vol`,
    `volSrc`,
    `uPrc`,
    `uPrcSrc`,
    `years`,
    `yearsSrc`,
    `sdiv`,
    `sdivSrc`,
    `rate`,
    `rateSrc`,
    `exType`,
    `exTypeSrc`,
    `effStrike`,
    `effStrikeSrc`,
    `symRatio`,
    `symRatioSrc`,
    `divString`,
    `divSrc`,
    `modelType`,
    `modelTypeSrc`,
    `calcPrecision`,
    `incGreeks`,
    `price`,
    `delta`,
    `gamma`,
    `theta`,
    `vega`,
    `volga`,
    `vanna`,
    `deDecay`,
    `rho`,
    `phi`,
    `error`,
    `pricerModel`,
    `timestamp`
FROM `SRAnalytics`.`MsgOptionCalculator`
WHERE 
    /* Replace with a VARCHAR(24) */ 
    `userName` = 'Example_userName'
  AND
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `okey_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `okey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `okey_tk` = 'Example_okey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `okey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_dy` = 1
  AND
    /* Replace with a DOUBLE */ 
    `okey_xx` = 4.56
  AND
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `okey_cp` = 'Call';

UPDATE TABLE EXAMPLE QUERY

UPDATE `SRAnalytics`.`MsgOptionCalculator` 
SET
    /* Replace with a DOUBLE */
    `vol` = 4.56,
    /* Replace with a ENUM('Default','User') */
    `volSrc` = 'Default',
    /* Replace with a DOUBLE */
    `uPrc` = 4.56,
    /* Replace with a ENUM('Default','User') */
    `uPrcSrc` = 'Default',
    /* Replace with a DOUBLE */
    `years` = 4.56,
    /* Replace with a ENUM('Default','User') */
    `yearsSrc` = 'Default',
    /* Replace with a DOUBLE */
    `sdiv` = 4.56,
    /* Replace with a ENUM('Default','User') */
    `sdivSrc` = 'Default',
    /* Replace with a DOUBLE */
    `rate` = 4.56,
    /* Replace with a ENUM('Default','User') */
    `rateSrc` = 'Default',
    /* Replace with a ENUM('None','American','European','Asian','Cliquet') */
    `exType` = 'None',
    /* Replace with a ENUM('Default','User') */
    `exTypeSrc` = 'Default',
    /* Replace with a DOUBLE */
    `effStrike` = 4.56,
    /* Replace with a ENUM('Default','User') */
    `effStrikeSrc` = 'Default',
    /* Replace with a DOUBLE */
    `symRatio` = 4.56,
    /* Replace with a ENUM('Default','User') */
    `symRatioSrc` = 'Default',
    /* Replace with a TINYTEXT */
    `divString` = 'dummy tiny text',
    /* Replace with a ENUM('Default','User') */
    `divSrc` = 'Default',
    /* Replace with a ENUM('None','LogNormalExact','NormalExact','LogNormalApprox','NormalApprox') */
    `modelType` = 'None',
    /* Replace with a ENUM('Default','User') */
    `modelTypeSrc` = 'Default',
    /* Replace with a ENUM('None','Low','Normal','High','Custom') */
    `calcPrecision` = 'Low',
    /* Replace with a ENUM('None','Yes','No') */
    `incGreeks` = 'No',
    /* Replace with a DOUBLE */
    `price` = 4.56,
    /* Replace with a FLOAT */
    `delta` = 1.23,
    /* Replace with a FLOAT */
    `gamma` = 1.23,
    /* Replace with a FLOAT */
    `theta` = 1.23,
    /* Replace with a FLOAT */
    `vega` = 1.23,
    /* Replace with a FLOAT */
    `volga` = 1.23,
    /* Replace with a FLOAT */
    `vanna` = 1.23,
    /* Replace with a FLOAT */
    `deDecay` = 1.23,
    /* Replace with a FLOAT */
    `rho` = 1.23,
    /* Replace with a FLOAT */
    `phi` = 1.23,
    /* Replace with a VARCHAR(32) */
    `error` = 'Example_error',
    /* Replace with a VARCHAR(8) */
    `pricerModel` = 'Example_pricerModel',
    /* Replace with a DATETIME(6) */
    `timestamp` = '2022-01-01 12:34:56.000000'
WHERE
    /* Replace with a VARCHAR(24) */ 
    `userName` = 'Example_userName'
  AND
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `okey_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `okey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `okey_tk` = 'Example_okey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `okey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_dy` = 1
  AND
    /* Replace with a DOUBLE */ 
    `okey_xx` = 4.56
  AND
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `okey_cp` = 'Call';

INSERT TABLE EXAMPLE QUERY

INSERT INTO `SRAnalytics`.`MsgOptionCalculator`(
    /* Replace with a VARCHAR(24) */ 
    `userName`,
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `okey_at`,
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `okey_ts`,
    /* Replace with a VARCHAR(12) */ 
    `okey_tk`,
    /* Replace with a SMALLINT UNSIGNED */ 
    `okey_yr`,
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_mn`,
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_dy`,
    /* Replace with a DOUBLE */ 
    `okey_xx`,
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `okey_cp`,
    /* Replace with a DOUBLE */
    `vol`,
    /* Replace with a ENUM('Default','User') */
    `volSrc`,
    /* Replace with a DOUBLE */
    `uPrc`,
    /* Replace with a ENUM('Default','User') */
    `uPrcSrc`,
    /* Replace with a DOUBLE */
    `years`,
    /* Replace with a ENUM('Default','User') */
    `yearsSrc`,
    /* Replace with a DOUBLE */
    `sdiv`,
    /* Replace with a ENUM('Default','User') */
    `sdivSrc`,
    /* Replace with a DOUBLE */
    `rate`,
    /* Replace with a ENUM('Default','User') */
    `rateSrc`,
    /* Replace with a ENUM('None','American','European','Asian','Cliquet') */
    `exType`,
    /* Replace with a ENUM('Default','User') */
    `exTypeSrc`,
    /* Replace with a DOUBLE */
    `effStrike`,
    /* Replace with a ENUM('Default','User') */
    `effStrikeSrc`,
    /* Replace with a DOUBLE */
    `symRatio`,
    /* Replace with a ENUM('Default','User') */
    `symRatioSrc`,
    /* Replace with a TINYTEXT */
    `divString`,
    /* Replace with a ENUM('Default','User') */
    `divSrc`,
    /* Replace with a ENUM('None','LogNormalExact','NormalExact','LogNormalApprox','NormalApprox') */
    `modelType`,
    /* Replace with a ENUM('Default','User') */
    `modelTypeSrc`,
    /* Replace with a ENUM('None','Low','Normal','High','Custom') */
    `calcPrecision`,
    /* Replace with a ENUM('None','Yes','No') */
    `incGreeks`,
    /* Replace with a DOUBLE */
    `price`,
    /* Replace with a FLOAT */
    `delta`,
    /* Replace with a FLOAT */
    `gamma`,
    /* Replace with a FLOAT */
    `theta`,
    /* Replace with a FLOAT */
    `vega`,
    /* Replace with a FLOAT */
    `volga`,
    /* Replace with a FLOAT */
    `vanna`,
    /* Replace with a FLOAT */
    `deDecay`,
    /* Replace with a FLOAT */
    `rho`,
    /* Replace with a FLOAT */
    `phi`,
    /* Replace with a VARCHAR(32) */
    `error`,
    /* Replace with a VARCHAR(8) */
    `pricerModel`,
    /* Replace with a DATETIME(6) */
    `timestamp`
) 
VALUES(
    'Example_userName',
    'None',
    'None',
    'Example_okey_tk',
    123,
    1,
    1,
    4.56,
    'Call',
    4.56,
    'Default',
    4.56,
    'Default',
    4.56,
    'Default',
    4.56,
    'Default',
    4.56,
    'Default',
    'None',
    'Default',
    4.56,
    'Default',
    4.56,
    'Default',
    'dummy tiny text',
    'Default',
    'None',
    'Default',
    'Low',
    'No',
    4.56,
    1.23,
    1.23,
    1.23,
    1.23,
    1.23,
    1.23,
    1.23,
    1.23,
    1.23,
    'Example_error',
    'Example_pricerModel',
    '2022-01-01 12:34:56.000000'
);

DELETE TABLE EXAMPLE QUERY

DELETE FROM `SRAnalytics`.`MsgOptionCalculator` 
WHERE
    /* Replace with a VARCHAR(24) */ 
    `userName` = 'Example_userName'
  AND
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `okey_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `okey_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `okey_tk` = 'Example_okey_tk'
  AND
    /* Replace with a SMALLINT UNSIGNED */ 
    `okey_yr` = 123
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_mn` = 1
  AND
    /* Replace with a TINYINT UNSIGNED */ 
    `okey_dy` = 1
  AND
    /* Replace with a DOUBLE */ 
    `okey_xx` = 4.56
  AND
    /* Replace with a ENUM('Call','Put','Pair') */ 
    `okey_cp` = 'Call';

Doc Columns Query

SELECT * FROM SRAnalytics.doccolumns WHERE TABLE_NAME='OptionCalculator' ORDER BY ordinal_position ASC;